﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using acessaDB;
using Frame;
using System.Data;
using System.Configuration;
using FirebirdSql.Data.FirebirdClient;
using Estrutura;


namespace Banco
{

    public class BD_Relperna
    {
        #region variaveis_globais

        acessaDB.conectaDBFireBird fireBird = new conectaDBFireBird();
        string conectionString = System.Configuration.ConfigurationManager.AppSettings["FireBirdConnectionLocal"];
        string usuario = string.Empty;

        #endregion

        #region construtor

        public BD_Relperna()
        {
            if (System.Configuration.ConfigurationManager.AppSettings["ambiente"] == "PROD")
                conectionString = System.Configuration.ConfigurationManager.AppSettings["FireBirdConnectionProd"];
        }
        public BD_Relperna(string usu_Login)
        {
            if (System.Configuration.ConfigurationManager.AppSettings["ambiente"] == "PROD")
                conectionString = System.Configuration.ConfigurationManager.AppSettings["FireBirdConnectionProd"];
            usuario = usu_Login;
        }

        #endregion

        #region metodos

        public DataSet _retorna_Relperna(
                                                string CTM_REL_PERNA_ID,
                                                string CTM_RELVOO_ID,
                                                string PER_DATA,
                                                string PER_DE,
                                                string PER_PARA,
                                                string PER_PARTIDA,
                                                string PER_DECOLAGEM,
                                                string PER_POUSO,
                                                string PER_CORTE,
                                                string PER_DIRUNO,
                                                string PER_NOTURNO,
                                                string PER_TOTAL
                                                )
        {

            if (string.IsNullOrEmpty(CTM_REL_PERNA_ID)) CTM_REL_PERNA_ID = "0";
            if (string.IsNullOrEmpty(CTM_RELVOO_ID)) CTM_RELVOO_ID = "0";
            if (string.IsNullOrEmpty(PER_DATA)) PER_DATA = Util.retonarAspas(Util.formataData(System.DateTime.Now.ToString()));
            if (string.IsNullOrEmpty(PER_DE)) PER_DE = "''"; else PER_DE = Util.retonarAspas(PER_DE);
            if (string.IsNullOrEmpty(PER_PARA)) PER_PARA = "''"; else PER_PARA = Util.retonarAspas(PER_PARA);
            if (string.IsNullOrEmpty(PER_PARTIDA)) PER_PARTIDA = "''"; else PER_PARTIDA = Util.retonarAspas(PER_PARTIDA);
            if (string.IsNullOrEmpty(PER_DECOLAGEM)) PER_DECOLAGEM = "''"; else PER_DECOLAGEM = Util.retonarAspas(PER_DECOLAGEM);
            if (string.IsNullOrEmpty(PER_POUSO)) PER_POUSO = "''"; else PER_POUSO = Util.retonarAspas(PER_POUSO);
            if (string.IsNullOrEmpty(PER_CORTE)) PER_CORTE = "''"; else PER_CORTE = Util.retonarAspas(PER_CORTE);
            if (string.IsNullOrEmpty(PER_DIRUNO)) PER_DIRUNO = "''"; else PER_DIRUNO = Util.retonarAspas(PER_DIRUNO);
            if (string.IsNullOrEmpty(PER_NOTURNO)) PER_NOTURNO = "''"; else PER_NOTURNO = Util.retonarAspas(PER_NOTURNO);
            if (string.IsNullOrEmpty(PER_TOTAL)) PER_TOTAL = "''"; else PER_TOTAL = Util.retonarAspas(PER_TOTAL);

            string sql = "SELECT " +
                                  "CTM_REL_PERNA.CTM_REL_PERNA_ID, " +
                                  "CTM_REL_PERNA.CTM_RELVOO_ID, " +
                                  "CTM_REL_PERNA.PER_DATA, " +
                                  "CTM_REL_PERNA.PER_DE, " +
                                  "CTM_REL_PERNA.PER_PARA, " +
                                  "CTM_REL_PERNA.PER_PARTIDA, " +
                                  "CTM_REL_PERNA.PER_DECOLAGEM, " +
                                  "CTM_REL_PERNA.PER_POUSO, " +
                                  "CTM_REL_PERNA.PER_CORTE, " +
                                  "CTM_REL_PERNA.PER_DIRUNO, " +
                                  "CTM_REL_PERNA.PER_NOTURNO, " +
                                  "CTM_REL_PERNA.PER_TOTAL, " +
                                  "CTM_REL_PERNA.PER_TOTAL_POUSO_DECOLAGEM, " +
                                  "CTM_REL_PERNA.PER_TOTAL_PARTIDA_CORTE, " +
                                  "(SELECT TEMPO FROM FN_FORMATAR_TIME(CTM_REL_PERNA.PER_TOTAL_PARTIDA_CORTE)) AS TOTAL " +
                            "FROM CTM_REL_PERNA " +
                            "LEFT JOIN CTM_RELVOO ON CTM_RELVOO.CTM_RELVOO_ID = CTM_REL_PERNA.CTM_RELVOO_ID " +
                            "WHERE " +
                            "      (CTM_REL_PERNA.CTM_REL_PERNA_ID                = " + CTM_REL_PERNA_ID + "  OR " + CTM_REL_PERNA_ID + " = 0) AND " +
                            "      (CTM_REL_PERNA.CTM_RELVOO_ID                  = " + CTM_RELVOO_ID + "  OR " + CTM_RELVOO_ID + " = 0) AND " +
                            "      (CTM_REL_PERNA.PER_DATA                        = " + PER_DATA + "  OR " + PER_DATA + " = " + PER_DATA + ") ";
            

            DataSet dsRetorno = fireBird.retornaDataSet(sql, conectionString);
            return dsRetorno;

        }


        public List<ES_Relperna> _obter_Relperna(
                                                string CTM_REL_PERNA_ID,
                                                string CTM_RELVOO_ID,
                                                string PER_DATA,
                                                string PER_DE,
                                                string PER_PARA,
                                                string PER_PARTIDA,
                                                string PER_DECOLAGEM,
                                                string PER_POUSO,
                                                string PER_CORTE,
                                                string PER_DIRUNO,
                                                string PER_NOTURNO,
                                                string PER_TOTAL
                                                )
        {

            if (string.IsNullOrEmpty(CTM_REL_PERNA_ID)) CTM_REL_PERNA_ID = "0";
            if (string.IsNullOrEmpty(CTM_RELVOO_ID)) CTM_RELVOO_ID = "0";
            if (string.IsNullOrEmpty(PER_DATA)) PER_DATA = Util.retonarAspas(Util.formataData(System.DateTime.Now.ToString()));
            if (string.IsNullOrEmpty(PER_DE)) PER_DE = "''"; else PER_DE = Util.retonarAspas(PER_DE);
            if (string.IsNullOrEmpty(PER_PARA)) PER_PARA = "''"; else PER_PARA = Util.retonarAspas(PER_PARA);
            if (string.IsNullOrEmpty(PER_PARTIDA)) PER_PARTIDA = "''"; else PER_PARTIDA = Util.retonarAspas(PER_PARTIDA);
            if (string.IsNullOrEmpty(PER_DECOLAGEM)) PER_DECOLAGEM = "''"; else PER_DECOLAGEM = Util.retonarAspas(PER_DECOLAGEM);
            if (string.IsNullOrEmpty(PER_POUSO)) PER_POUSO = "''"; else PER_POUSO = Util.retonarAspas(PER_POUSO);
            if (string.IsNullOrEmpty(PER_CORTE)) PER_CORTE = "''"; else PER_CORTE = Util.retonarAspas(PER_CORTE);
            if (string.IsNullOrEmpty(PER_DIRUNO)) PER_DIRUNO = "''"; else PER_DIRUNO = Util.retonarAspas(PER_DIRUNO);
            if (string.IsNullOrEmpty(PER_NOTURNO)) PER_NOTURNO = "''"; else PER_NOTURNO = Util.retonarAspas(PER_NOTURNO);
            if (string.IsNullOrEmpty(PER_TOTAL)) PER_TOTAL = "''"; else PER_TOTAL = Util.retonarAspas(PER_TOTAL);

            string sql = "SELECT " +
                                  "CTM_REL_PERNA.CTM_REL_PERNA_ID, " +
                                  "CTM_REL_PERNA.CTM_RELVOO_ID, " +
                                  "CTM_REL_PERNA.PER_DATA, " +
                                  "CTM_REL_PERNA.PER_DE, " +
                                  "CTM_REL_PERNA.PER_PARA, " +
                                  "CTM_REL_PERNA.PER_PARTIDA, " +
                                  "CTM_REL_PERNA.PER_DECOLAGEM, " +
                                  "CTM_REL_PERNA.PER_POUSO, " +
                                  "CTM_REL_PERNA.PER_CORTE, " +
                                  "CTM_REL_PERNA.PER_DIRUNO, " +
                                  "CTM_REL_PERNA.PER_NOTURNO, " +
                                  "CTM_REL_PERNA.PER_TOTAL, " +
                                  "CTM_REL_PERNA.PER_TOTAL_POUSO_DECOLAGEM, " +
                                  "CTM_REL_PERNA.PER_TOTAL_PARTIDA_CORTE, " +
                                  "(SELECT TEMPO FROM FN_FORMATAR_TIME(CTM_REL_PERNA.PER_TOTAL_PARTIDA_CORTE)) AS TOTAL_PARTIDA_CORTE, " +
                                  "(SELECT TEMPO FROM FN_FORMATAR_TIME(CTM_REL_PERNA.PER_TOTAL_POUSO_DECOLAGEM)) AS TOTAL_POUSO_DECOLAGEM " +
                            "FROM CTM_REL_PERNA " +
                            "LEFT JOIN CTM_RELVOO ON CTM_RELVOO.CTM_RELVOO_ID = CTM_REL_PERNA.CTM_RELVOO_ID " +
                            "WHERE " +
                            "      (CTM_REL_PERNA.CTM_REL_PERNA_ID                = " + CTM_REL_PERNA_ID + "  OR " + CTM_REL_PERNA_ID + " = 0) AND " +
                            "      (CTM_REL_PERNA.CTM_RELVOO_ID                  = " + CTM_RELVOO_ID + "  OR " + CTM_RELVOO_ID + " = 0) AND " +
                            "      (CTM_REL_PERNA.PER_DATA                        = " + PER_DATA + "  OR " + PER_DATA + " = " + PER_DATA + ") ";
                            /*"      (CTM_REL_PERNA.PER_DE                          = " + PER_DE + "  OR " + PER_DE + " = '') AND " +
                            "      (CTM_REL_PERNA.PER_PARA                        = " + PER_PARA + "  OR " + PER_PARA + " = '') AND " +
                            "      (CTM_REL_PERNA.PER_PARTIDA                     = " + PER_PARTIDA + "  OR " + PER_PARTIDA + " = '') AND " +
                            "      (CTM_REL_PERNA.PER_DECOLAGEM                   = " + PER_DECOLAGEM + "  OR " + PER_DECOLAGEM + " = '') AND " +
                            "      (CTM_REL_PERNA.PER_POUSO                       = " + PER_POUSO + "  OR " + PER_POUSO + " = '') AND " +
                            "      (CTM_REL_PERNA.PER_CORTE                       = " + PER_CORTE + "  OR " + PER_CORTE + " = '') AND " +
                            "      (CTM_REL_PERNA.PER_DIRUNO                      = " + PER_DIRUNO + "  OR " + PER_DIRUNO + " = '') AND " +
                            "      (CTM_REL_PERNA.PER_NOTURNO                     = " + PER_NOTURNO + "  OR " + PER_NOTURNO + " = '') AND " +
                            "      (CTM_REL_PERNA.PER_TOTAL                       = " + PER_TOTAL + "  OR " + PER_TOTAL + " = '') ";*/

            List<ES_Relperna> lista = new List<ES_Relperna>();
            FbConnection fbConexao = new FbConnection(conectionString);
            FbDataReader dr = fireBird.retornaDataReader(sql, conectionString, fbConexao);
            try
            {
                while (dr.Read())
                {
                    ES_Relperna item = new ES_Relperna();
                    item.CTM_REL_PERNA_ID = dr["CTM_REL_PERNA_ID"].ToString();
                    item.CTM_RELVOO_ID = dr["CTM_RELVOO_ID"].ToString();
                    item.PER_DATA = Util.formataData(dr["PER_DATA"].ToString(),"dd/MM/yyyy");
                    item.PER_DE = dr["PER_DE"].ToString();
                    item.PER_PARA = dr["PER_PARA"].ToString();
                    item.PER_PARTIDA = Util.formataTime(dr["PER_PARTIDA"].ToString(), ":", true);
                    item.PER_DECOLAGEM = Util.formataTime(dr["PER_DECOLAGEM"].ToString(), ":", true);
                    item.PER_POUSO = Util.formataTime(dr["PER_POUSO"].ToString(), ":", true);
                    item.PER_CORTE = Util.formataTime(dr["PER_CORTE"].ToString(), ":", true);
                    item.PER_DIRUNO = Util.formataTime(dr["PER_DIRUNO"].ToString(), ":", true);
                    item.PER_NOTURNO = Util.formataTime(dr["PER_NOTURNO"].ToString(), ":", true);
                    item.PER_TOTAL = dr["TOTAL_PARTIDA_CORTE"].ToString();
                    item.PER_TOTAL_PARTIDA_CORTE = dr["TOTAL_PARTIDA_CORTE"].ToString();
                    item.PER_TOTAL_POUSO_DECOLAGEM = dr["TOTAL_POUSO_DECOLAGEM"].ToString();
                    
                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                fireBird.fecharConexao(fbConexao);
            }
        }


        public ES_Relperna _obter_Relperna_by_id(
                                                string CTM_REL_PERNA_ID
                                                )
        {

            if (string.IsNullOrEmpty(CTM_REL_PERNA_ID)) CTM_REL_PERNA_ID = "'0'";

            string sql = "SELECT " +
                                  "CTM_REL_PERNA.CTM_REL_PERNA_ID, " +
                                  "CTM_REL_PERNA.CTM_RELVOO_ID, " +
                                  "CTM_REL_PERNA.PER_DATA, " +
                                  "CTM_REL_PERNA.PER_DE, " +
                                  "CTM_REL_PERNA.PER_PARA, " +
                                  "CTM_REL_PERNA.PER_PARTIDA, " +
                                  "CTM_REL_PERNA.PER_DECOLAGEM, " +
                                  "CTM_REL_PERNA.PER_POUSO, " +
                                  "CTM_REL_PERNA.PER_CORTE, " +
                                  "CTM_REL_PERNA.PER_DIRUNO, " +
                                  "CTM_REL_PERNA.PER_NOTURNO, " +
                                  "CTM_REL_PERNA.PER_TOTAL " +
                            "FROM CTM_REL_PERNA " +
                            "LEFT JOIN CTM_RELVOO ON CTM_RELVOO.CTM_RELVOO_ID = CTM_REL_PERNA.CTM_RELVOO_ID " +
                            "WHERE " +
                            "      (CTM_REL_PERNA.CTM_REL_PERNA_ID = " + CTM_REL_PERNA_ID + "  OR " + CTM_REL_PERNA_ID + " = '0') ";

            ES_Relperna item = new ES_Relperna();
            FbConnection fbConexao = new FbConnection(conectionString);
            FbDataReader dr = fireBird.retornaDataReader(sql, conectionString, fbConexao);
            try
            {
                while (dr.Read())
                {
                    item.CTM_REL_PERNA_ID = dr["CTM_REL_PERNA_ID"].ToString();
                    item.CTM_RELVOO_ID = dr["CTM_RELVOO_ID"].ToString();
                    item.PER_DATA = dr["PER_DATA"].ToString();
                    item.PER_DE = dr["PER_DE"].ToString();
                    item.PER_PARA = dr["PER_PARA"].ToString();
                    item.PER_PARTIDA = dr["PER_PARTIDA"].ToString();
                    item.PER_DECOLAGEM = dr["PER_DECOLAGEM"].ToString();
                    item.PER_POUSO = dr["PER_POUSO"].ToString();
                    item.PER_CORTE = dr["PER_CORTE"].ToString();
                    item.PER_DIRUNO = dr["PER_DIRUNO"].ToString();
                    item.PER_NOTURNO = dr["PER_NOTURNO"].ToString();
                    item.PER_TOTAL = dr["PER_TOTAL"].ToString();

                }
                return item;
            }
            finally
            {
                fireBird.fecharConexao(fbConexao);
            }
        }


        public void _inserir_Relperna(
                                                string CTM_REL_PERNA_ID,
                                                string CTM_RELVOO_ID,
                                                string PER_DATA,
                                                string PER_DE,
                                                string PER_PARA,
                                                string PER_PARTIDA,
                                                string PER_DECOLAGEM,
                                                string PER_POUSO,
                                                string PER_CORTE,
                                                string PER_DIRUNO,
                                                string PER_NOTURNO,
                                                string PER_TOTAL
                                                )
        {

            string sql = " INSERT INTO CTM_REL_PERNA " +
                         "      ( " +
                         "          CTM_RELVOO_ID                 , " +
                         "          PER_DATA                       , " +
                         "          PER_DE                         , " +
                         "          PER_PARA                       , " +
                         "          PER_PARTIDA                    , " +
                         "          PER_DECOLAGEM                  , " +
                         "          PER_POUSO                      , " +
                         "          PER_CORTE                      , " +
                         "          PER_DIRUNO                     , " +
                         "          PER_NOTURNO                    , " +
                         "          PER_TOTAL                        " +
                 "      )" +
                 "  VALUES " +
                 "      ( " +
                              Util.retonarAspas(CTM_RELVOO_ID, true) + ", " +
                              Util.retonarAspas(Util.formataData(PER_DATA), true) + ", " +
                              Util.retonarAspas(PER_DE, true) + ", " +
                              Util.retonarAspas(PER_PARA, true) + ", " +
                              Util.retonarAspas(Util.formataTime(PER_PARTIDA), true) + ", " +
                              Util.retonarAspas(Util.formataTime(PER_DECOLAGEM), true) + ", " +
                              Util.retonarAspas(Util.formataTime(PER_POUSO), true) + ", " +
                              Util.retonarAspas(Util.formataTime(PER_CORTE), true) + ", " +
                              Util.retonarAspas(Util.formataTime(PER_DIRUNO), true) + ", " +
                              Util.retonarAspas(Util.formataTime(PER_NOTURNO), true) + ", " +
                              Util.retonarAspas(string.Empty/*Util.formataTime(PER_TOTAL)*/, true) +
                 "      )";
            try
            {
                fireBird._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        public void _atualizar_Relperna(
                                                string CTM_REL_PERNA_ID,
                                                string CTM_RELVOO_ID,
                                                string PER_DATA,
                                                string PER_DE,
                                                string PER_PARA,
                                                string PER_PARTIDA,
                                                string PER_DECOLAGEM,
                                                string PER_POUSO,
                                                string PER_CORTE,
                                                string PER_DIRUNO,
                                                string PER_NOTURNO,
                                                string PER_TOTAL
                                                )
        {

            string sql = " UPDATE CTM_REL_PERNA SET " +
                         "      CTM_RELVOO_ID                  = " + Util.retonarAspas(CTM_RELVOO_ID, true) + ", " +
                         "      PER_DATA                        = " + Util.retonarAspas(Util.formataData(PER_DATA), true) + ", " +
                         "      PER_DE                          = " + Util.retonarAspas(PER_DE, true) + ", " +
                         "      PER_PARA                        = " + Util.retonarAspas(PER_PARA, true) + ", " +
                         "      PER_PARTIDA                     = " + Util.retonarAspas(Util.formataTime(PER_PARTIDA), true) + ", " +
                         "      PER_DECOLAGEM                   = " + Util.retonarAspas(Util.formataTime(PER_DECOLAGEM), true) + ", " +
                         "      PER_POUSO                       = " + Util.retonarAspas(Util.formataTime(PER_POUSO), true) + ", " +
                         "      PER_CORTE                       = " + Util.retonarAspas(Util.formataTime(PER_CORTE), true) + ", " +
                         "      PER_DIRUNO                      = " + Util.retonarAspas(Util.formataTime(PER_DIRUNO), true) + ", " +
                         "      PER_NOTURNO                     = " + Util.retonarAspas(Util.formataTime(PER_NOTURNO), true) + ", " +
                         "      PER_TOTAL                       = " + Util.retonarAspas(string.Empty/*Util.formataTime(PER_TOTAL)*/, true) +
                         " WHERE CTM_REL_PERNA_ID =  " + CTM_REL_PERNA_ID;
            try
            {
                fireBird._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        public void _excluir_Relperna(string CTM_REL_PERNA_ID)
        {
            string sql = "DELETE FROM CTM_REL_PERNA WHERE CTM_REL_PERNA_ID = " + CTM_REL_PERNA_ID;
            try
            {
                fireBird._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        #endregion
    }
}
